# 班级管理模块功能接口
import numpy as np
from flask import request, jsonify, send_from_directory, Blueprint
from flask_jwt_extended import jwt_required, get_jwt
import exts
import sqlite3
import pandas as pd
import os

classes_blueprint = Blueprint('classes', __name__)


# 班级上传模板下载
@classes_blueprint.route('/download/class-template/<string:template_code>', methods=['GET'])
@jwt_required()
@exts.permission_required('classes', 'R')
def download_class_template(template_code):
    """根据请求参数提供相应的班级信息上传模板下载"""
    template_filenames = {
        "1": "班级上传模板（通用）.xlsx",
    }
    template_filename = template_filenames.get(template_code)
    if not template_filename:
        # 如果提供的代码不在映射表中，返回错误信息
        return jsonify({
            "code": 400,
            "details": "提供的模板代码无效",
            "message": "请求参数错误",
            "status": "error"
        }), 400

    template_path = os.path.join("static/upload-templates/class-templates", template_filename)
    if not os.path.exists(template_path):
        # 如果文件不存在，返回错误信息
        return jsonify({
            "code": 404,
            "details": f"模板文件'{template_filename}'不存在",
            "message": "请求参数错误",
            "status": "error"
        }), 404

    try:
        return send_from_directory("static/upload-templates/class-templates", template_filename, as_attachment=True)
    except Exception as e:
        return jsonify({
            "code": 500,
            "details": str(e),
            "message": "下载模板时发生错误",
            "status": "error"
        }), 500


# 页面手动新增班级信息
@classes_blueprint.route('/add', methods=['POST'])
@jwt_required()
@exts.permission_required('classes', 'C')
def class_add():
    """手动新增班级信息"""
    data = {
        "school_id": request.json.get('school_id', None),
        "level_department": request.json.get('level_department', None),
        "class": request.json.get('class', None),
        "subject_selection": request.json.get('subject_selection', None),
        "status": request.json.get('status', None)
    }
    if data['class'] is None or data['subject_selection'] is None:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "缺少参数",
            "details": "缺少必要的字段, 请确保 class 和 subject_selection 不能为空"
        }), 400
    if data['school_id'] is None:
        claims = get_jwt()
        data['school_id'] = claims['school_id']
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 检查是否存在重复的班级
        check_sql = ('SELECT * FROM Classes WHERE school_id = ? AND level_department = ? AND class = ? '
                     'AND subject_selection = ?')
        cur.execute(check_sql, (data['school_id'], data['level_department'], data['class'],
                                data['subject_selection']))
        existing_class = cur.fetchone()
        if existing_class:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "班级信息重复",
                "details": "已存在相同的班级信息"
            }), 400
        sql = f'INSERT INTO Classes(school_id, level_department, class, subject_selection, status) ' \
              f'VALUES (?, ?, ?, ?, ?)'
        cur.execute(sql, (data['school_id'], data['level_department'], data['class'],
                          data['subject_selection'], data['status']))
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "班级信息添加成功",
            "data": {
                'id': cur.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 模板批量导入班级信息
@classes_blueprint.route('/import', methods=['POST'])
@jwt_required()
@exts.permission_required('classes', 'C')
def import_class():
    """批量导入班级信息"""
    school_id = request.form.get('school_id', None)
    if school_id is None:
        claims = get_jwt()
        school_id = claims['school_id']
    uploaded_files = request.files.getlist('files')
    if not uploaded_files or uploaded_files[0].filename == '':
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未上传任何文件"
        }), 400
    results = []
    for uploaded_file in uploaded_files:
        if not uploaded_file.filename.endswith('.xlsx'):
            results.append({
                "filename": uploaded_file.filename,
                "status": "error",
                "message": "请求参数错误",
                "details": "上传的文件不是有效的 Excel 文件, 请上传后缀名为 .xlsx 的文件"
            })
            continue
        conn = None
        try:
            df = pd.read_excel(uploaded_file, sheet_name='Sheet1')
            df.columns = range(len(df.columns))
            df.rename(columns={0: 'level_department', 1: 'class', 2: 'subject_selection', 3: 'status'}, inplace=True)

            conn = exts.get_db()
            cursor = conn.cursor()
            duplicate_classes = []

            for i in range(len(df)):
                level_department = df.loc[i, 'level_department']
                class_value = int(df.loc[i, 'class'])
                subject_selection = df.loc[i, 'subject_selection']
                status = df.loc[i, 'status'] if not pd.isnull(df.loc[i, 'status']) else None

                # 检查是否存在重复的班级
                check_sql = ('SELECT * FROM Classes WHERE school_id = ? AND level_department = ? AND class = ? '
                             'AND subject_selection = ?')
                cursor.execute(check_sql, (school_id, level_department, class_value, subject_selection))
                existing_class = cursor.fetchone()

                if existing_class:
                    duplicate_classes.append({
                        "school_id": school_id,
                        "level_department": level_department,
                        "class": class_value,
                        "subject_selection": subject_selection,
                        "status": existing_class['status']
                    })
                    continue

                # 插入新班级
                if status is None:
                    sql = ('INSERT INTO Classes(school_id, level_department, class, subject_selection) '
                           'VALUES (?, ?, ?, ?)')
                    params = (school_id, level_department, class_value, subject_selection)
                else:
                    sql = ('INSERT INTO Classes(school_id, level_department, class, subject_selection, status) '
                           'VALUES (?, ?, ?, ?, ?)')
                    params = (school_id, level_department, class_value, subject_selection, status)

                cursor.execute(sql, params)

            conn.commit()
            cursor.close()
            successful_imports = len(df) - len(duplicate_classes)
            response_temp = {
                "filename": uploaded_file.filename,
                "status": "success",
                "message": "班级信息导入成功",
                "details": f"{uploaded_file.filename}文件中, 成功导入{successful_imports}条班级信息数据"
            }
            if duplicate_classes:
                response_temp['duplicates'] = duplicate_classes
            results.append(response_temp)
        except Exception as e:
            if conn:
                conn.rollback()
            results.append({
                "filename": uploaded_file.filename,
                "status": "error",
                "message": f"班级信息导入失败: {str(e)}"
            })
        finally:
            if conn:
                exts.close_db(conn)

    response = {
        "code": 207,  # 207 Multi-Status 表示多状态响应
        "status": "multi-status",
        "results": results
    }
    return jsonify(response), 207


# 班级信息更新
@classes_blueprint.route('/update/<id_>', methods=['PUT'])
@jwt_required()
@exts.permission_required('classes', 'U')
def class_import(id_):
    """更新班级信息"""
    valid_params = ['school_id', 'level_department', 'class', 'subject_selection', 'status']
    required_params = ['class', 'subject_selection']
    data = {key: request.json.get(key) for key in valid_params}

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400

    if data['school_id'] is None:
        claims = get_jwt()
        data['school_id'] = claims['school_id']

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()

        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE Classes SET {set_clause} WHERE id = ?'
        params = tuple(data.values()) + (id_,)
        cur.execute(sql, params)
        if cur.rowcount == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "更新失败",
                "details": f"没有找到 id 为 {id_} 的记录。"
            }), 404
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "班级信息更新成功",
            "data": {
                'id': id_
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": f"班级信息更新失败: {str(e)}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 班级状态更新
@classes_blueprint.route('/status', methods=['PATCH'])
@jwt_required()
@exts.permission_required('classes', 'A')
def update_status():
    """更新班级状态"""
    data = request.json.get('data', None)
    if data is None:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的请求参数"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        results = []
        for id_ in data:
            sql = "SELECT status FROM Classes WHERE id = ?"
            cur.execute(sql, (id_,))
            current_status = cur.fetchone()
            if not current_status:
                results.append({
                    "id": id_,
                    "status": "error",
                    "message": "更新失败",
                    "details": f"没有找到 id 为 {id_} 的记录"
                })
                continue
            new_status = "active" if current_status["status"] == "inactive" else "inactive"
            sql = "UPDATE Classes SET status = ? WHERE id = ?"
            cur.execute(sql, (new_status, id_))
            results.append({
                "id": id_,
                "status": "success",
                "message": "更新成功",
                "details": f"班级状态由 {current_status['status']} 更新为 {new_status}"
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "results": results
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": f"班级状态更新失败: {str(e)}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 班级信息查询
@classes_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('classes', 'R')
def query_classes():
    """组合条件查询班级信息"""
    school_id = request.args.get('school_id', get_jwt()['school_id'], type=int)
    level_department = request.args.get('level_department', None, type=str)
    subject_selection = request.args.get('subject_selection', None, type=str)
    class_ = request.args.get('class', None, type=str)
    status = request.args.get('status', 'active', type=str)
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)

    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 参数必须大于 0"
        }), 400

    offset = (page - 1) * page_size

    conditions = []
    if school_id:
        conditions.append(f"c.school_id = {school_id}")
    if level_department:
        conditions.append(f"c.level_department LIKE '%{level_department}%'")
    if subject_selection:
        conditions.append(f"c.subject_selection LIKE '%{subject_selection}%'")
    if class_:
        conditions.append(f"c.class = '{class_}'")
    if status:
        conditions.append(f"c.status = '{status}'")

    where_clause = " AND ".join(conditions) if conditions else "1=1"

    sql = f"""
        SELECT DISTINCT c.*, s.school_name
        FROM Classes c
        LEFT JOIN School s ON c.school_id = s.school_id
        WHERE {where_clause}
        ORDER BY c.id DESC
        LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(DISTINCT c.id)
        FROM Classes c
        LEFT JOIN School s ON c.school_id = s.school_id
        WHERE {where_clause}
    """

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(sql_count)
        total_records = cursor.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询数据
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的班级信息"
            }), 404

        results = [dict(zip([column[0] for column in cursor.description], row)) for row in results]

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()
